#!/usr/bin/env python3
# -*- coding: utf-8 -*-
from unittest.case import TestCase
from bzjsons import Jsoner

__project_ = 'bzjsons'
__file_name__ = 'sqlalchemy_objects_test'
__author__ = 'bright.zhang'
__time__ = '2020/6/28 1:31'


# 部门实体类，测试用
class Department(object):
    def __init__(self, id_=None, name_=None, pid_=None, head_=None, enabled_=True, remark_=None, deleted_=False):
        # 初始化
        self.id = id_
        self.name = name_
        self.pid = pid_
        self.head = head_
        self.enabled = enabled_
        self.remark = remark_
        self.deleted = deleted_

    def __str__(self):
        return '%s (%d-%s)' % (self.name, self.id, self.head)


# 本地测试数据库的用户和密码，请设置
DB_USR = 'root'
DB_PWD = 'Br1ghtMySQL'
DB_URL = "mysql+mysqlconnector://%s:%s@localhost:3306/testingxxx123" % (DB_USR, DB_PWD)


# The test must be executed manually, for referenced to MySQL.
# 这个测试是用于在SqlAlchemy查询所有数据并做序列化时的报错，主要原因是将私有成员也做了处理的缘故
# 这个测试搭建了SqlAlchemy的环境，做了相应的测试，并通过了验证。 by Bright Zhang on 2020/06/28.
class SqlAlchemyObjectsTest(TestCase):
    def setUp(self) -> None:
        from sqlalchemy import create_engine
        from sqlalchemy_utils import create_database, database_exists
        # hardcoded here
        global DB_USR, DB_PWD, DB_URL
        self.db_url = DB_URL
        # pip install sqlalchemy
        # pip install PyMySQL
        # pip install mysql-connector-python
        # pip install mysql-connector
        # pip install sqlalchemy_utils
        # 创建数据库
        import sqlalchemy as sa
        from sqlalchemy.orm import mapper
        self.engine = create_engine(self.db_url, max_overflow=5, echo=True)
        if not database_exists(self.engine.url):
            create_database(self.engine.url)

        # 建表： Department
        self.department_metadata = sa.MetaData()
        self.department_table = sa.Table('department', self.department_metadata,
                                         sa.Column('id', sa.Integer, primary_key=True, autoincrement=True),
                                         sa.Column('pid', sa.Integer, nullable=False),
                                         sa.Column('name', sa.String(100), nullable=False),
                                         sa.Column('head', sa.String(50), nullable=False),
                                         sa.Column('enabled', sa.Boolean, nullable=False, default=True),
                                         sa.Column('deleted', sa.Boolean, nullable=False, default=False),
                                         sa.Column('remark', sa.String(800), nullable=True))
        mapper(Department, self.department_table)
        self.department_metadata.create_all(self.engine)
        # 写入两条数据
        from sqlalchemy.orm import sessionmaker
        session_class = sessionmaker(bind=self.engine)
        self.session = session_class()

        self.dep1 = Department(name_='Operations', pid_=-1, head_='bright', enabled_=True, deleted_=False, remark_=None)
        self.dep2 = Department(name_='Finance', pid_=-1, head_='lucy', enabled_=True, deleted_=False, remark_=None)
        self.session.add(self.dep1)
        self.session.add(self.dep2)
        self.session.commit()
        self.deps_all = self.session.query(Department).all()

    def tearDown(self) -> None:
        # 删除测试数据库
        from sqlalchemy_utils import drop_database
        drop_database(self.db_url)

    def testObjectsFiltered(self):
        import traceback
        try:
            deps_json = Jsoner.dumps(self.deps_all)
            self.assertIsNotNone(deps_json)
            print(deps_json)
        except:
            traceback.print_exc()

